//    Copyright (C) Kherty.  All rights reserved.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics.Contracts;
using System.IO.Packaging;
using System.Threading;
using System.Xml;
using OpenLS.Core.Serialization;
using OpenLS.Drawing.Serialization;
using OpenLS.Spreadsheet.Data;
using OpenLS.Spreadsheet.Drawing;
using OpenLS.Spreadsheet.Internals;
using OpenLS.Spreadsheet.Pivot;
using OpenLS.Spreadsheet.Serialization;

namespace OpenLS.Spreadsheet
{
    partial class Worksheet
    {
        private readonly List<ExtensionElement> alternateContents = new List<ExtensionElement>();

        internal override string UriPrefix
        {
            get { return "/xl/worksheets/sheet"; }
        }

        internal override string ContentType
        {
            get { return SpreadsheetConstants.WorksheetContentType; }
        }

        internal override string RelationshipType
        {
            get { return SpreadsheetConstants.WorksheetRelationshipType; }
        }

        internal override void ReadXmlCore(ReadContext context)
        {
            Contract.Requires(ActivePane != null);
            Contract.Ensures(ActivePane != null);
            TopLeftPane.DisableEvents();
            context.AddData(this);
            using (this.CreateCellValueChangedDisabler()){
            using (ReadContext c = context.Read(xmlName))
            {
                serializedGuid = c.GetOptionalGuid("guid");
                if (serializedGuid != null)
                    guid = serializedGuid.Value;
                Properties = c.ReadOptionalElement<SheetProperties>("sheetPr");
                c.EatIf("dimension");
                Contract.Assert(ActivePane != null);
                if (c.ReaderLocalName == "sheetViews")
                {
                    using (ReadContext c2 = c.Read("sheetViews"))
                    {
                        while (c2.ReaderLocalName == "sheetView")
                        {
                            Contract.Assert(ActivePane != null);

                            View = c2.ReadElement(() => new WorksheetView(this));
                            View.WorkbookViewId = 0;
                            //\\hack

                            if (View.TopRightSelection != null)
                                TopRightPane = new WorksheetPane(this);
                            if (View.BottomLeftSelection != null)
                                BottomLeftPane = new WorksheetPane(this);
                            if (View.BottomRightSelection != null)
                                BottomRightPane = new WorksheetPane(this);
                            if (ActivePane == null)
                            {
                                switch (View.Pane.ActivePane)
                                {
                                    case PaneType.BottomLeft:
                                        if (BottomLeftPane == null)
                                            BottomLeftPane = new WorksheetPane(this);
                                        Contract.Assert(ActivePane != null);
                                        break;
                                    case PaneType.TopRight:
                                        if (TopRightPane == null)
                                            TopRightPane = new WorksheetPane(this);
                                        break;
                                    case PaneType.BottomRight:
                                        if (BottomRightPane == null)
                                            BottomRightPane = new WorksheetPane(this);
                                        break;
                                }
                            }
                            Contract.Assert(ActivePane != null);
                            //\\ ActivePane = TopLeftPane;

                            Contract.Assert(ActivePane != null);
                            if (View.Pane != null)
                            {
                                Pane pane = View.Pane;
                                switch (pane.State)
                                {
                                    case PaneState.Frozen:
                                    case PaneState.FrozenSplit:
                                        {
                                            if (pane.TopLeftCell == null)
                                                break;
                                            CellAddress address;
                                            if (pane.ColumnCount != null || pane.RowCount != null)
                                            {
                                                FreezePanes(pane.RowCount, pane.ColumnCount);
                                            }
                                            if (Workbook.NamingConvention.TryParse(pane.TopLeftCell, out address))
                                            {
                                                if (TopRightPane != null)
                                                    TopRightPane.Left = address.Column;
                                                if (BottomLeftPane != null)
                                                    BottomLeftPane.Top = address.Row;
                                                if (BottomRightPane != null)
                                                {
                                                    BottomRightPane.Top = address.Row;
                                                    BottomRightPane.Left = address.Column;
                                                }
                                            }
                                        }
                                        // TopLeftPane.SplitWidth = pane.XSplit;
                                        //TopLeftPane.SplitHeight = pane.YSplit;
                                        break;
                                    case PaneState.Split:
                                        {
                                            if (pane.TopLeftCell == null)
                                                break;
                                            CellAddress address;
                                            if (Workbook.NamingConvention.TryParse(pane.TopLeftCell, out address))
                                            {
                                                if (TopRightPane != null)
                                                    TopRightPane.Left = address.Column;
                                                if (BottomLeftPane != null)
                                                    BottomLeftPane.Top = address.Row;
                                                if (BottomRightPane != null)
                                                {
                                                    BottomRightPane.Top = address.Row;
                                                    BottomRightPane.Left = address.Column;
                                                }
                                            }
                                        }

                                        break;
                                }
                                Contract.Assert(ActivePane != null);
                            }
                        }
                    }
                }
                Contract.Assert(ActivePane != null);
                _sheetFormatPr = c.ReadOptionalElement<SheetFormatProperties>("sheetFormatPr");
                //\\_columns = new ColumnCollection(this); //\\
                if (c.ReaderLocalName == "cols")
                {
                    _columns.ReadXml(c);
                }
                if (c.ReaderLocalName == "sheetData")
                {
                    using (ReadContext cSheetData = context.Read("sheetData"))
                    {
                        var stack = new GroupBuilderStack<Row>(RootRowGroup,
                                                               (parent, first, last) =>
                                                               RowGroup.CreateColumnGroupWithLast
                                                                   (parent, (Row) first,
                                                                    (Row) last));
                        var styleRecordManager = context.GetData<StyleRecordManager>();
                        Contract.Assert(styleRecordManager != null);
                        if (ReadRowsAsync(stack, cSheetData, styleRecordManager)) return;
                        stack.Close();
                        Rows.ForgetIntervalManager();
                    }
                }
                foreach (WorksheetPane pane in GetPanes())
                    pane.RefreshWindow();
                Contract.Assert(ActivePane != null);

                if (_view != null)
                {
                    _view.PostReadXml();
                    if (_view.Selection != null)
                        Selection = _view.Selection;
                    //\\ GetRange(_view.Selection.Value); //\\ Selection = Range.FromAddress(this, view.Selection.Value);
                    if (_view.ActiveCellPosition != null)
                        ActiveCell = this[_view.ActiveCellPosition.Value];
                    if (TopLeftPane != null && TopRightPane == null && BottomRightPane == null)
                    {
                        WorksheetPane topLeftPane = TopLeftPane;
                        if (_view._topLeftCache != null)
                        {
                            CellAddress a = CellAddress.Parse(_view._topLeftCache);
                            topLeftPane.Top = a.Row;
                            topLeftPane.Left = a.Column;
                        }
                    }
                }
                Contract.Assert(ActivePane != null);
                if (ActiveCell == null)
                {
                    ActiveCell = ActivePane.TopLeftCell;
                    Selection = ActiveCell.ToRange();
                }

                CalculationProperties = c.ReadOptionalElement<WorksheetCalculationProperties>("sheetCalcPr");
                Protection = c.ReadOptionalElement<SheetProtection>("sheetProtection");
                ProtectedRanges = c.ReadOptionalListElement<ProtectedRange>("protectedRanges", "protectedRange");
                Scenarios = c.ReadOptionalElement<Scenarios>("scenarios");
                AutoFilter = c.ReadOptionalElement<AutoFilter>("autoFilter");
                _sortStates = c.ReadOptionalWhile<SortState>("sortState");
                DataConsolidate = c.ReadOptionalElement<DataConsolidate>("dataConsolidate");
                _customSheetViews = c.ReadOptionalListElement<CustomSheetView>("customSheetViews", "customSheetView");
                if (context.ReaderLocalName == "mergeCells")
                    mergeCells(context);
                PhoneticProperties = c.ReadOptionalElement<PhoneticProperties>("phoneticPr");
                ClearConditionalFormatting();
                while (c.ReaderLocalName == "conditionalFormatting")
                {
                    ConditionalFormatting cf;
                    try
                    {
                        cf = c.ReadOptionalElement<ConditionalFormatting>("conditionalFormatting");
                    }
                    catch (IgnoreElementException)
                    {
                        continue;
                    }
                    AddConditionalFormatting(cf);
                }
                foreach (
                    DataValidation dataValidation in
                        c.ReadOptionalListElement<DataValidation>("dataValidations", "dataValidation"))
                    DataValidations.Add(dataValidation);
                //DataValidations.AddRange(c.ReadOptionalListElement<DataValidation>("dataValidations", "dataValidation"));
                object Hyperlinks = c.ReadOptionalListElement<Hyperlink>("hyperlinks", "hyperlink");

                PrintOptions = c.ReadOptionalElement<PrintOptions>("printOptions");
                PageMargins = c.ReadOptionalElement<PageMargins>("pageMargins");
                PageSetup = c.ReadOptionalElement<PageSetup>("pageSetup");
                HeaderFooter = c.ReadOptionalElement<HeaderFooter>("headerFooter");
                RowBreaks.Clear();
                List<Break> brs = c.ReadOptionalListElement<Break>("rowBreaks", "brk");
                if (brs != null)
                    RowBreaks.AddRange(brs);

                ColumnBreaks.Clear();
                brs = c.ReadOptionalListElement<Break>("colBreaks", "brk");
                if (brs != null)
                    ColumnBreaks.AddRange(brs);
                _customProperties = c.ReadOptionalListElement<CustomProperty>("customProperties", CustomProperty.xmlName, () => new CustomProperty());
                _cellWatches = new List<CellWatch>(c.ReadOptionalListElement<CellWatch>("cellWatches", "cellWatch"));

                _ignoredErrors =
                    new List<IgnoredError>(c.ReadOptionalListElement<IgnoredError>("ignoredErrors", "ignoredError"));
                c.WarnIf("smartTags");
                if (c.ReaderLocalName == "drawing")
                {
                    using (ReadContext cdrawing = context.Read("drawing"))
                    {
                        PackageRelationship relationship =
                            c.Part.GetRelationship(cdrawing.GetString(OfficeConstants.RelationshipNamespace + "id"));
                        PackagePart p =
                            cdrawing.Part.Package.GetPart(PackUriHelper.ResolvePartUri(cdrawing.Part.Uri,
                                                                                       relationship.TargetUri));
                        using (var drawingReader = new XmlTextReader(p.GetStream()))
                        {
                            var drawing = new Drawing.Drawing(this);
                            var drawingc = new ReadContext(drawingReader, p,
                                                           SpreadsheetConstants.SpreadsheetDrawingNamespaceUri);
                            drawingc.CopyDataFrom(context);
                            drawing.ReadXml(drawingc);
                            foreach (Anchor a in drawing.Anchors)
                                Anchors.Add(a);
                        }
                    }
                }
                _legacyVmlDrawing = LegacyDrawingHelper.ReadOptionalVmlElementAs(c, "legacyDrawing");
                _legacyVmlDrawingHF = LegacyDrawingHelper.ReadOptionalVmlElementAs(c, "legacyDrawingHF");
                Background = PictureHelper.ReadOptionalPicture(c);
                c.Eat("oleObjects");
                c.Eat("controls");

                _webPublishItems.Clear();
                _webPublishItems.AddRange(ItemsHelper.ReadItems<WebPublishItem>(c, "webPublishItems"));
                _tables.Clear();
                IEnumerable<TablePart> parts = c.ReadOptionalCollection<TablePart>("tableParts", "tablePart");
                if (parts != null)
                {
                    foreach (TablePart pp in parts)
                    {
                        using (
                            ReadContext tableContext = c.OpenRelatedContext(context.Part.GetRelationship(pp.Id),
                                                                            context.NamespaceUri))
                        {
                            _tables.Add(tableContext.ReadElement(() => new Table()));
                        }
                    }
                }
                ReadExtensions(c);
                while (c.ReaderLocalName == "AlternateContent")
                    alternateContents.Add(c.ReadElement<ExtensionElement>());
            }
            ReadQueryTables(context);
            ReadComments(context);
            TopLeftPane.EnableEvents();
            foreach (ConditionalFormatting f in ConditionalFormatting)
                f.Apply(Spreadsheet.ConditionalFormatting.ApplyReason.Added);
            ReadPivotTables(context);
        }
    }

        private bool ReadRowsSync(GroupBuilderStack<Row> stack, ReadContext context,
                              StyleRecordManager styleRecordManager)
        {
            int rowCounter = 0;
            int previousIndex = -1;
            while (context.ReaderLocalName == "row")
            {
                var attributes = new Row.RowAttributes();
                ReadContext rc = attributes.ReadAttribute(context, previousIndex, styleRecordManager);
                Row row = Rows[attributes.Index];
                attributes.Apply(row, stack);
                row.ReadXml(context, previousIndex, rc, styleRecordManager);
                previousIndex = row.Index;
                foreach (Cell cell in row.GetNonEmptyCells())
                {
                    NonVolatileCells.AddNoRow(cell);
                    cell.Column.Add(cell);
                }
                rowCounter++;
                if (rowCounter%50 == 0)
                {
                    var w = context.GetData<BackgroundWorker>();
                    if (w != null && w.WorkerReportsProgress)
                        w.ReportProgress(rowCounter/20);
                    if (!context.IsValid)
                        return true;
                }
            }
            return false;
        }

        private bool ReadRowsAsync(GroupBuilderStack<Row> stack, ReadContext context,
                      StyleRecordManager styleRecordManager)
        {
            int previousIndex = -1;
            RowReader reader = new RowReader();
            Thread thread = new Thread( new ThreadStart(delegate
                                                            {
                                                                var item = reader.GetItem();
                                                                WorkbookResolver workbookResolver = context.GetData<WorkbookResolver>();
                                                                while (true)
                                                                {
                                                                    while (item == null)
                                                                    {
                                                                        if (reader.IsTerminated)
                                                                            return;
                                                                        Thread.Sleep(1);
                                                                        item = reader.GetItem();
                                                                    }
                                                                    item.Apply(this, stack, styleRecordManager, workbookResolver);
                                                                    item = reader.GetItem();
                                                                    
                                                                }

                                                            }));
            thread.Start();
            int rowCounter = 0;
            while (context.ReaderLocalName == "row")
            {
                var attributes = new Row.RowAttributes();
                ReadContext rc = attributes.ReadAttribute(context, previousIndex, styleRecordManager);
                Row.RowSerializationData data = Row.ReadXmlData(previousIndex, rc, styleRecordManager);
                data.attributes = attributes;
                reader.Add(data);
                rowCounter++;
                if (rowCounter%50 == 0)
                {
                    var w = context.GetData<BackgroundWorker>();
                    if (w != null && w.WorkerReportsProgress)
                        w.ReportProgress(rowCounter/20);
                    if (!context.IsValid)
                    {
                        reader.Terminate();
                        return true;
                    }
                }
            }
            reader.Terminate();
            thread.Join();
            return false;
        }


        private void ReadPivotTables(ReadContext context)
        {
#if !DEBUG
            return;
#endif
            foreach (PackageRelationship relationship in
                context.Part.GetRelationshipsByType(SpreadsheetConstants.PivotTableRelationship))
            {
                using (
                    ReadContext ctx = context.OpenRelatedContext(relationship,
                                                                 SpreadsheetConstants.SpreadsheetNamespaceUri))
                {
                    _pivotTables.Add(ctx.ReadElement(() => new PivotTableDefinition(this)));
                }
            }
        }

        private void ReadExtensions(ReadContext context)
        {
            if (context.ReaderLocalName != "extLst")
                return;
            using (ReadContext c = context.Read("extLst"))
            {
                c.PassAttributes();
                while (c.ReaderLocalName == "ext")
                {
                    string uri = c.GetOptionalString("uri");
                    if (uri == SparkLineGroup.Uri)
                    {
                        using (ReadContext ext = context.Read("ext"))
                        {
                            SparkLineGroups.Clear();
                            SparkLineGroups.AddRange(
                                ext.ReadElements<SparkLineGroup>(
                                    SpreadsheetConstants.MicrosoftSpreadsheetNamespace + "sparklineGroups",
                                    SparkLineGroup.xmlName));
                        }
                    }
                    else
                    {
                        Extensions.Add(context.ReadElement<ExtensionElement>());
                    }
                }
            }
        }

        private void
            ClearConditionalFormatting()
        {
            var l = new List<ConditionalFormatting>(ConditionalFormatting);
            foreach (ConditionalFormatting c in l)
                RemoveConditionalFormatting(c);
        }

        private void ReadComments(ReadContext context)
        {
            using (
                ReadContext ctx = context.GetRelatedContext(SpreadsheetConstants.CommentsRelationsipType,
                                                            context.NamespaceUri))
            {
                if (ctx == null)
                    return;
                var comments = new InternalComments();
                comments.ReadXml(ctx);
                comments.ApplyTo(this);
            }
        }


        private void ReadQueryTables(ReadContext context)
        {
            QueryTables.Clear();
            foreach (
                PackageRelationship relationship in
                    context.Part.GetRelationshipsByType(SpreadsheetConstants.QueryTableRelationshipType))
            {
                using (
                    ReadContext ctx = context.OpenRelatedContext(relationship,
                                                                 SpreadsheetConstants.SpreadsheetNamespaceUri))
                {
                    QueryTables.Add(ctx.ReadElement<QueryTable>());
                }
            }
        }

        internal override void WriteXmlCore(WriteContext context)
        {
            WriteComments(context);
            using (WriteContext c = context.Write(xmlName))
            {
                c.SetOptionalGuid("guid", serializedGuid);
                c.WriteOptionalElement(Properties);
                c.WarnIf("dimension");
                if (_view != null)
                {
                    _view.PreWrite(this);
                    _view.TabSelected = Workbook.View.ActiveSheet == this;
                    using (c.Write("sheetViews"))
                    {
                        c.WriteOptionalElement(View);
                    }
                }
                c.WriteOptionalElement(FormatProperties);
                c.WriteElement(Columns);
                c.SetData(typeof (SharedFormulaManager), new SharedFormulaManager());
                using (WriteContext sheetDataContext = c.Write("sheetData"))
                {
                    foreach (Row row in Rows)
                    {
                        row.WriteXml(sheetDataContext);
                    }
                }
                c.WriteOptionalElement(CalculationProperties);
                c.WriteOptionalElement(_worksheetProtection);
                c.WriteOptionalElementList(ProtectedRanges, "protectedRanges")
                    ;
                c.WriteOptionalElement(Scenarios);
                c.WriteOptionalElement(AutoFilter);
                if (_sortStates != null)
                    c.WriteElements(_sortStates);
//                    foreach (SortState s in _sortStates)
                //                      c.WriteElement(s);
                c.WriteOptionalElement(DataConsolidate);
                if (CustomSheetViews.Count > 0)
                    c.WriteOptionalElementList(CustomSheetViews, "customSheetViews");
                WriteMergeCellsXml(c);
                c.WriteOptionalElement(PhoneticProperties);
                foreach (ConditionalFormatting f in ConditionalFormatting)
                    c.WriteElement(f);
                if (DataValidations.Count > 0)
                    c.WriteOptionalElementList(DataValidations, "dataValidations");
                var _hyperlinks = new List<Hyperlink>();
                foreach (Cell cell in NonVolatileCells)
                {
                    Hyperlink l = cell.Hyperlink;
                    if (l != null)
                        _hyperlinks.Add(l);
                }
                c.WriteOptionalElementList(_hyperlinks, "hyperlinks");
                c.WriteOptionalElement(PrintOptions);
                c.WriteOptionalElement(PageMargins);
                c.WriteOptionalElement(_pageSetup);
                c.WriteOptionalElement(HeaderFooter);
                PageBreaks.WriteBreaks(RowBreaks, c, "rowBreaks");
                PageBreaks.WriteBreaks(ColumnBreaks, c, "columnBreaks");
                c.WriteOptionalElementList(CustomProperties, "customProperties");
                c.WriteOptionalElementList(CellWatches, "cellWatches");
                if (IgnoredErrors.Count > 0)
                {
                    using (WriteContext c2 = c.Write("ignoredErrors"))
                    {
                        foreach (IgnoredError er in IgnoredErrors)
                            c2.WriteElement(er);
                    }
                }
                c.WarnIf("smartTags");
                if (Anchors.Count > 0)
                {
                    var drawing = new Drawing.Drawing(this);
                    foreach (Anchor a in Anchors)
                        drawing.Anchors.Add(a);
                    //\\foreach (Drawing drawing in Drawings)
                    {
                        using (WriteContext drawingE = c.Write(SpreadsheetConstants.Spreadsheet + "drawing"))
                        {
                            var drawingUri =
                                new Uri("/xl/drawings/drawing" + (Workbook.Sheets.IndexOf(this) + 1) + ".xml",
                                        UriKind.Relative); //\\
                            using (
                                RelatedWriteContext drawingContext =
                                    drawingE.CreateRelatedContext(DrawingConstants.DrawingNamespace,
                                                                  drawingUri, DrawingConstants.DrawingRelationshipType,
                                                                  DrawingConstants.DrawingContentType))
                            {
                                drawing.WriteXml(drawingContext);
                                drawingE.SetString(OfficeConstants.RelationshipNamespace + "id",
                                                   drawingContext.Relationship.Id);
                            }
                            /*PackagePart drawingPart = context.Part.Package.CreatePart(drawingUri, DrawingConstants.DrawingContentType, CompressionOption.Maximum);
                            Uri uri = PackUriHelper.GetRelativeUri(context.Part.Uri, drawingUri);
                            PackageRelationship rel = context.Part.CreateRelationship(uri, TargetMode.Internal, DrawingConstants.DrawingRelationshipType);
                            drawingE.SetString(OfficeConstants.RelationshipNamespace + "id", rel.Id);
                            drawing.WritePart(drawingPart);
                             */
                        }
                    }
                }
                LegacyDrawingHelper.WriteOptionalVmlElementAs(_legacyVmlDrawing, c, "legacyDrawing");
                LegacyDrawingHelper.WriteOptionalVmlElementAs(_legacyVmlDrawingHF, c, "legacyDrawingHF");
                PictureHelper.WriteOptionalPicture(c, Background);
                c.WarnIf("oleObjects");
                c.WarnIf("controls");
                ItemsHelper.WriteItems(c, _webPublishItems, "webPublishItems");
                if (_tables.Count > 0)
                {
                    using (WriteContext wc = context.Write("tableParts"))
                    {
                        wc.SetDouble("count", _tables.Count);
                        foreach (Table table in Tables)
                        {
                            table.Id = wc.GetData<TableCounter>().NextId;
                            using (
                                RelatedWriteContext ctx = context.CreateRelatedContext(null,
                                                                                       SpreadsheetConstants.
                                                                                           SpreadsheetNamespaceUri,
                                                                                       context.GetAvailableUri(
                                                                                           "/xl/tables/table{0}.xml"),
                                                                                       SpreadsheetConstants.
                                                                                           TableRelationshipType,
                                                                                       SpreadsheetConstants.
                                                                                           TableContentType))
                            {
                                ctx.WriteElement(table);
                                var tablePart = new TablePart {Id = ctx.Relationship.Id};
                                wc.WriteElement(tablePart);
                            }
                            //tableId++;
                        }
                    }
                }
                WriteExtensions(c);
            }
            WriteQueryTables(context);
            WritePivotTables(context);
        }

        private void WriteExtensions(WriteContext context)
        {
            if (SparkLineGroups.Count == 0 && Extensions.Count == 0)
                return;
            using (WriteContext cExtLst = context.Write("extLst"))
            {
                foreach (ExtensionElement extension in Extensions)
                    cExtLst.WriteElement(extension);
                if (SparkLineGroups.Count > 0)
                {
                    using (WriteContext ext2 = cExtLst.Write("ext"))
                    {
                        ext2.SetString("uri", SparkLineGroup.Uri);
                        using (
                            WriteContext spareLineGroupsElement =
                                ext2.Write(SpreadsheetConstants.MicrosoftSpreadsheetNamespace + "sparklineGroups"))
                        {
                            spareLineGroupsElement.WriteElements(SparkLineGroups);
                        }
                    }
                }
            }
        }


        private void WriteComments(WriteContext context)
        {
            var comments = new InternalComments();
            if (comments.BuildFrom(this))
            {
                using (WriteContext ctx = context.CreateRelatedContext(SpreadsheetConstants.Spreadsheet,
                                                                       context.GetAvailableUri("/xl/comments{0}.xml"),
                                                                       SpreadsheetConstants.CommentsRelationsipType,
                                                                       SpreadsheetConstants.CommentsContentType))
                {
                    comments.WriteXml(ctx);
                }
            }
        }

        private void WriteMergeCellsXml(WriteContext writer)
        {
            bool hasSpan = false;
            foreach (Cell c in NonVolatileCells)
                if (c.HasSpan)
                {
                    hasSpan = true;
                    break;
                }
            if (!hasSpan)
                return;
            using (WriteContext mergeCells = writer.Write("mergeCells"))
            {
                foreach (Cell c in NonVolatileCells)
                {
                    if (c.HasSpan)
                    {
                        using (WriteContext mergeCell = mergeCells.Write("mergeCell"))
                        {
                            mergeCell.SetString("ref", c.GetRangeAddress().Label);
                        }
                    }
                }
            }
        }

        private void WriteQueryTables(WriteContext context)
        {
            foreach (QueryTable queryTable in QueryTables)
            {
                using (
                    RelatedWriteContext wc = context.CreateRelatedContext(null,
                                                                          SpreadsheetConstants.SpreadsheetNamespaceUri,
                                                                          context.GetAvailableUri(
                                                                              "/xl/queryTables/queryTable{0}.xml"),
                                                                          SpreadsheetConstants.
                                                                              QueryTableRelationshipType,
                                                                          SpreadsheetConstants.QueryTableContentType))
                {
                    wc.WriteElement(queryTable);
                }
            }
        }

        private void WritePivotTables(WriteContext context)
        {
            foreach (PivotTableDefinition pivotTableDefinition in _pivotTables)
            {
                using (
                    RelatedWriteContext wc = context.CreateRelatedContext(null,
                                                                          SpreadsheetConstants.SpreadsheetNamespaceUri,
                                                                          context.GetAvailableUri(
                                                                              "/xl/pivotTables/pivotTables{0}.xml"),
                                                                          SpreadsheetConstants.PivotTableRelationship,
                                                                          SpreadsheetConstants.PivotTableContentType))
                {
                    wc.WriteElement(pivotTableDefinition);
                }
            }
        }
    }
}